import matplotlib as mpl
mpl.rcParams['font.sans-serif'] = ['SimHei']
mpl.rcParams['axes.unicode_minus'] = False
import pymysql
import matplotlib.pyplot as plt
import numpy as np

def generate_bar_chart():
    conn = pymysql.connect(
        host='10.114.183.17',
        user='labuser',
        password='123456',
        db='exceltodb',
        port=3306,
        charset='utf8'
    )
    cursor = conn.cursor()
    query = """
    SELECT Product, SUM(Test_hour * Coefficient) as total_hours
    FROM assignment
    WHERE Date BETWEEN '2025-07-01' AND '2025-09-30' AND product != '' and test_category!='Operation' and special !='s'
    GROUP BY Product
    ORDER BY total_hours DESC
    LIMIT 10
    """
    cursor.execute(query)
    results = cursor.fetchall()
    products = [row[0] for row in results]
    hours = [row[1] for row in results]
    conn.close()
    x = np.arange(len(products))
    plt.figure(figsize=(12, 6))
    plt.bar(x, hours, color='skyblue')
    plt.xticks(x, products, rotation=45, ha='right')
    plt.xlabel('产品名称')
    plt.ylabel('总工时')
    plt.title('2025年三季度产品工时分布')
    plt.tight_layout()
    plt.show()

generate_bar_chart()